﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace PoliAsis.Domain.Common
{



    public class QueryGenerator<T>
    {
        QueryMap querymap = QueryMap.Map;
        private static string SELECT_QUERY = "SELECT {0} FROM {1}";
        private static string SELECT_WHERE_QUERY = "SELECT {0} FROM {1} WHERE {2}";
        private static string INSERT_QUERY = "INSERT INTO {0}({1}) VALUES ({2});select @@identity as id";
        private static string INSERT_DATA_TABLE_QUERY = "INSERT INTO {0}({1}) SELECT {2} FROM @dtTable";
        private static string UPDATE_QUERY = "UPDATE {0} SET {1} WHERE {2}";
        private static string DELETE_QUERY = "DELETE {0} WHERE {1}";
        private static string PAGING_QUERY = @"WITH V_DATA AS ( SELECT COUNT({0}) OVER() as TotalRows,
                                              ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum, * 
                                              FROM {1}
                                              {2}) SELECT TotalRows, {3} FROM V_DATA
                                              WHERE V_DATA.RowNum between @start_row and @end_row";

        private QueryDesc Query(T dt_object, List<T> list_dt_object, QUERY_TYPE qtype, int start_row = 1, int end_row = 10, SEARCH_MODE search_mode = SEARCH_MODE.SEARCH_CONTAINS)
        {
            QueryDesc q = new QueryDesc();

            List<SqlParameter> param_collection = new List<SqlParameter>();
            List<SqlParameter> key_param_collection = new List<SqlParameter>();

            List<string> header_list = new List<string>();//select columns
            List<string> value_list = new List<string>();//parameters
            List<string> update_list = new List<string>();//update or where list
            List<string> search_list = new List<string>();//update list
            List<string> where_list = new List<string>();//where list
            List<string> where_key_list = new List<string>();//where list
            foreach (PropertyInfo pi in dt_object.GetType().GetProperties())
            {
                if (pi.IsNonPersistent()) continue;

                object value = pi.GetValue(dt_object, null);
                string column_name = "";
                string property_name = "";
                if (value != null && !String.IsNullOrEmpty(value.ToString()))
                {
                    property_name = pi.Name;
                    column_name = querymap.GetColumnName(typeof(T), property_name);

                    header_list.Add(column_name);
                    value_list.Add("@" + property_name);
                    where_list.Add(column_name + " = @" + property_name);
                    search_list.Add(Expression(pi, value, property_name, column_name, search_mode));

                    param_collection.Add(CreateParametar("@" + property_name, value));
                    //exclude key from update
                    if (!querymap.GetTableKeys(typeof(T)).Contains(property_name))
                    {
                        update_list.Add(column_name + " = @" + property_name);
                    }
                    else
                    {
                        key_param_collection.Add(CreateParametar("@" + property_name, value));
                        where_key_list.Add(column_name + " = @" + property_name);
                    }

                }
                else
                    continue;

            }

            switch (qtype)
            {
                case QUERY_TYPE.SELECT:
                    q.query = string.Format(SELECT_QUERY,
                                    querymap.GetTableColumnsCsep(typeof(T)),
                                    querymap.GetTableName(typeof(T)));
                    return q;

                case QUERY_TYPE.SELECT_WHERE:
                    q.query = string.Format(SELECT_WHERE_QUERY,
                                    querymap.GetTableColumnsCsep(typeof(T)),
                                    querymap.GetTableName(typeof(T)),
                                    string.Join(" and ", where_list));
                    q.AddParameters(param_collection);
                    return q;

                case QUERY_TYPE.INSERT:
                    q.query = string.Format(INSERT_QUERY,
                                    querymap.GetTableName(typeof(T)),
                                    string.Join(",", header_list),
                                    string.Join(",", value_list));
                    q.AddParameters(param_collection);
                    return q;

                case QUERY_TYPE.INSERT_DATA_TABLE:
                    q.query = string.Format(INSERT_DATA_TABLE_QUERY,
                                    querymap.GetTableName(typeof(T)),
                                    string.Join(",", header_list),
                                    string.Join(",", header_list));
                    DataTable dt = ConvertListToDataTable(list_dt_object);
                    q.AddParameter(CreateParametar("@dtTable", dt, SqlDbType.Structured, querymap.GetDataTableType(typeof(T))));
                    return q;

                case QUERY_TYPE.UPDATE:
                    q.query = string.Format(UPDATE_QUERY,
                                    querymap.GetTableName(typeof(T)),
                                    string.Join(",", update_list),
                                    string.Join(" and ", where_key_list));
                    q.AddParameters(param_collection);
                    return q;

                case QUERY_TYPE.DELETE:
                    q.query = string.Format(DELETE_QUERY,
                                    querymap.GetTableName(typeof(T)),
                                    string.Join(" and ", where_key_list));
                    q.AddParameters(key_param_collection);
                    return q;

                case QUERY_TYPE.SELECT_PAGING:
                    string where = string.Join(" and ", where_list);
                    q.query = string.Format(PAGING_QUERY, querymap.GetTableKeysCsep(typeof(T)),
                                                 querymap.GetTableName(typeof(T)),
                                                 String.IsNullOrEmpty(where) ? String.Empty : " WHERE " + where,
                                                 querymap.GetTableColumnsCsep(typeof(T)));
                    param_collection.Add(CreateParametar("@start_row", start_row));
                    param_collection.Add(CreateParametar("@end_row", end_row));
                    q.AddParameters(param_collection);
                    return q;

                case QUERY_TYPE.SEARCH_PAGING:
                    string search = string.Join(" and ", search_list);
                    q.query = string.Format(PAGING_QUERY, querymap.GetTableKeysCsep(typeof(T)),
                                                 querymap.GetTableName(typeof(T)),
                                                 String.IsNullOrEmpty(search) ? String.Empty : " WHERE " + search,
                                                 querymap.GetTableColumnsCsep(typeof(T)));
                    param_collection.Add(CreateParametar("@start_row", start_row));
                    param_collection.Add(CreateParametar("@end_row", end_row));
                    q.AddParameters(param_collection);
                    return q;
            }

            return q;
        }
        public QueryDesc SelectQuery(T dt_object)
        {
            return Query(dt_object, null, QUERY_TYPE.SELECT);
        }
        public QueryDesc SelectWhereQuery(T dt_object)
        {
            return Query(dt_object, null, QUERY_TYPE.SELECT_WHERE);
        }
        public QueryDesc InsertQuery(T dt_object)
        {
            return Query(dt_object, null, QUERY_TYPE.INSERT);
        }
        public QueryDesc InsertDtQuery(List<T> list_dt_object)
        {
            var o = list_dt_object[0];
            //we must send the first object, because the query generator
            //have to know how to generate the columns
            return Query(o, list_dt_object, QUERY_TYPE.INSERT_DATA_TABLE);
        }
        public QueryDesc UpdateQuery(T dt_object)
        {
            return Query(dt_object, null, QUERY_TYPE.UPDATE);
        }
        public QueryDesc DeleteQuery(T dt_object)
        {
            return Query(dt_object, null, QUERY_TYPE.DELETE);
        }
        public QueryDesc SelectQueryPaging(T dt_object, int start_row, int end_row)
        {
            return Query(dt_object, null, QUERY_TYPE.SELECT_PAGING, start_row, end_row);
        }
        public QueryDesc SearchQueryPaging(T dt_object, int start_row, int end_row, SEARCH_MODE search_mode = SEARCH_MODE.SEARCH_CONTAINS)
        {
            return Query(dt_object, null, QUERY_TYPE.SEARCH_PAGING, start_row, end_row, search_mode);
        }


        /// <summary>
        /// Returns a proper expression based on the data type of the property
        /// </summary>
        /// <param name="pi"></param>
        /// <param name="data_object"></param>
        /// <returns></returns>
        private string Expression(PropertyInfo pi, object data_object, string prop_name, string column_name, SEARCH_MODE mode)
        {
            StringBuilder sb = new StringBuilder();

            string data_type = pi.PropertyType.ToString().ToLower();
            if (data_type.Contains("string"))
            {
                if (mode == SEARCH_MODE.SEARCH_CONTAINS)
                    sb.AppendLine(column_name + " like '%' + @" + prop_name + " + '%' ");
                if (mode == SEARCH_MODE.SEARCH_BEGINS_WITH)
                    sb.AppendLine(column_name + " like  @" + prop_name + " + '%' ");
                if (mode == SEARCH_MODE.SEARCH_ENDS_WITH)
                    sb.AppendLine(column_name + " like  @ + '%' " + prop_name);
            }
            else
                if (data_type.Contains("int"))
                {
                    sb.AppendLine(column_name + " =  @" + prop_name);
                }

            return sb.ToString();
        }
        public void SetObjectFromDataRow(object o, SqlDataReader reader)
        {
            foreach (PropertyInfo pi in o.GetType().GetProperties())
            {
                if (pi.IsNonPersistent()) continue;
                string prop_name_mapped = querymap.GetColumnName(typeof(T), pi.Name);
                object value = reader[prop_name_mapped];
                if (value != null && !String.IsNullOrEmpty(value.ToString()))
                {
                    pi.SetValue(o, value, null);
                }
            }
        }
        public DataTable ConvertListToDataTable(List<T> list)
        {
            DataTable dt = new DataTable();

            //add columns
            foreach (PropertyInfo pi in list[0].GetType().GetProperties())
            {
                if (pi.IsNonPersistent()) continue;
                DataColumn dc = new DataColumn();
                dc.ColumnName = pi.Name;
                dc.DataType = Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType;
                dt.Columns.Add(dc);
            }

            //add rows
            foreach (T item in list)
            {
                DataRow dr = dt.NewRow();
                foreach (PropertyInfo pi in list[0].GetType().GetProperties())
                {
                    dr[pi.Name] = pi.GetValue(item, null);
                }

                dt.Rows.Add(dr);
            }

            return dt;
        }
        public SqlParameter CreateParametar(string name, object value, SqlDbType sql_type, string type_name)
        {
            SqlParameter p = new SqlParameter();
            p.Direction = ParameterDirection.Input;
            p.ParameterName = name;
            p.Value = value ?? DBNull.Value;
            p.SqlDbType = sql_type;
            p.TypeName = type_name;
            return p;
        }
        public SqlParameter CreateParametar(string name, object value)
        {
            SqlParameter p = new SqlParameter();
            p.Direction = ParameterDirection.Input;
            p.ParameterName = name;
            p.Value = value ?? DBNull.Value;
            return p;
        }
    }

    public class QueryDesc
    {
        public string query;
        private List<SqlParameter> _parameters;

        public QueryDesc()
        {
            _parameters = new List<SqlParameter>();
        }
        public SqlParameter[] parameters
        {
            get { return _parameters.ToArray(); }
        }

        public void AddParameters(List<SqlParameter> parameter_list)
        {
            _parameters.AddRange(parameter_list);
        }
        public void AddParameter(SqlParameter parameter)
        {
            _parameters.Add(parameter);
        }
    }

    public enum QUERY_TYPE
    {
        SELECT,
        SELECT_WHERE,
        INSERT,
        INSERT_DATA_TABLE,
        UPDATE,
        DELETE,
        SEARCH,
        SEARCH_PAGING,
        SELECT_PAGING
    }

}
